#if UNITY_EDITOR
using UnityEditor;
using UnityEngine;
using System.IO;
using OfficeOpenXml;
using System;
using System.Reflection;
using System.Collections.Generic;
using System.Data;
using Excel;
using System.Linq;
using System.Collections;


namespace ZPlugin.Tool
{

public static class ExcelTool
{

    /// <summary>
    /// excel文件存放路径
    /// </summary>
    public static string EXCEL_PATH = Application.dataPath + "/GameData/";

    /// <summary>
    /// 数据结构类存放路径
    /// </summary>
    public static string DATA_CLASS_PATH = Application.dataPath + "/Scripts/ExcelData/";
    /// <summary>
    /// 容器类 
    /// </summary>
    public static string DATA_CONTAINER_PATH = Application.dataPath + "/Scripts/ExcelData/";


    /// <summary>
    /// 内容开始行
    /// </summary>
    public const int BEGINE_ROW = 4;
    /// <summary>
    /// 字段所在行
    /// </summary>
    public const int FIELD_ROW = 2;
    /// <summary>
    /// 类型所在行
    /// </summary>
    public const int TYPE_ROW = 3;


    /// <summary>
    /// 生成excel数据结构
    /// </summary>
    [MenuItem("Tools/表格处理/生成excel数据结构")]
    private static void GenerateExcelInfo()
    {
        DirectoryInfo dInfo = new DirectoryInfo(EXCEL_PATH);
        FileInfo[] files = dInfo.GetFiles("*.xlsx", SearchOption.AllDirectories).Where(f => !f.Name.StartsWith("~$")).ToArray(); ;

        foreach (var file in files)
        {
            using (var package = new ExcelPackage(file))
            {
                string fileName = file.Name.Replace(file.Extension, "");
                foreach (var worksheet in package.Workbook.Worksheets)
                {
                    GenerateClass(worksheet, fileName);
                    break;
                }

                string assetPath = "Assets" + file.FullName.Replace(Application.dataPath.Replace("/", "\\"), "").Replace("\\", "/");
                GenerateContainer(fileName, assetPath);
            }
        }
    }

    private static void GenerateClass(ExcelWorksheet worksheet, string fileName)
    {
        string className = fileName;
        string str = "[System.Serializable]\npublic class " + className + "\n{\n";

        for (int i = 1; i <= worksheet.Dimension.End.Column; i++)
        {
            string fieldName = worksheet.Cells[FIELD_ROW, i].Value.ToString();
            string fieldType = worksheet.Cells[TYPE_ROW, i].Value.ToString();
            str += "\t public " + fieldType + " " + fieldName + ";\n";
        }

        str += "}";

        string path = Path.Combine(DATA_CLASS_PATH, className + ".cs");
        File.WriteAllText(path, str);

        AssetDatabase.Refresh();
    }

    private static void GenerateContainer(string fileName, string excelFilePath)
    {
        string className = fileName + "DataSO";
        string str = "using System.Collections.Generic;\nusing UnityEngine;\nusing UnityEditor;\n\n";
        str += "[CreateAssetMenu(fileName = \"" + className + "\", menuName = \"ExcelData/" + className + "\")]\n";
        str += "public class " + className + " : ScriptableObject\n{\n";
        str += "\tpublic List<" + fileName + "> data = new List<" + fileName + "" + ">();\n\n";
        str += "\t[MenuItem(\"Tools/表格处理/导入表格数据\")]\n";
        str += "\tpublic static void ImportData()\n";
        str += "\t{\n";
        str += "\t\tExcelTool.ImportExcelData<" + className + ", " + fileName + ">(\"" + excelFilePath + "\");\n";
        str += "\t}\n";
        str += "}";

        string path = Path.Combine(DATA_CONTAINER_PATH, className + ".cs");
        File.WriteAllText(path, str);

        AssetDatabase.Refresh();
    }

    /// <summary>
    /// 获取变量名所在行,应对不同规则
    /// </summary>
    /// <param name="table"></param>
    /// <param name="index"></param>
    /// <returns></returns>
    private static DataRow GetVariableNameRow(DataTable table)
    {
        return table.Rows[FIELD_ROW];
    }
    /// <summary>
    /// 获取变量类型所在行
    /// </summary>
    /// <param name="table"></param>
    /// <returns></returns>
    private static DataRow GetVariableTypeRow(DataTable table)
    {
        return table.Rows[TYPE_ROW];
    }





    /// <summary>
    /// 导入excel  需要手动指定excel路径
    /// </summary>
    /// <typeparam name="T">最终保存的so类型</typeparam>
    /// <typeparam name="U">List列表元素的类型</typeparam>
    /// <param name="excelFilePath"></param>
    public static void ImportExcelData<T, U>(string excelFilePath, Action<T> OnFinish = null) where T : ScriptableObject
    {
        if (string.IsNullOrEmpty(excelFilePath))
        {
            Debug.LogError("Excel file  empty.");
            return;
        }

        FileInfo fileInfo = new FileInfo(excelFilePath);
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
            {
                // 如果整个sheet是空的，跳过
                if (worksheet.Dimension == null)
                {
                    continue;
                }

                string directoryPath = Path.GetDirectoryName(excelFilePath);
                string soPath = Path.Combine(directoryPath, worksheet.Name + ".asset");

                T excelData = AssetDatabase.LoadAssetAtPath<T>(soPath);
                if (excelData == null)
                {
                    excelData = ScriptableObject.CreateInstance<T>();
                    AssetDatabase.CreateAsset(excelData, soPath);
                }

                Type type = typeof(U);

                IList list = null;
                foreach (FieldInfo field in typeof(T).GetFields())
                {
                    if (field.FieldType.IsGenericType && field.FieldType.GetGenericTypeDefinition() == typeof(List<>))
                    {
                        // 找到list泛型字段
                        list = (IList)field.GetValue(excelData);
                        list.Clear(); // 清空列表，以便重新填充
                    }
                }

                // 自定义开始列和  字段名所在列  
                for (int i = BEGINE_ROW; i <= worksheet.Dimension.End.Row; i++)
                {
                    // 检查第一列是否为空
                    if (worksheet.Cells[i, 1].Value == null || string.IsNullOrEmpty(worksheet.Cells[i, 1].Value.ToString()))
                    {
                        continue;
                    }

                    object dataItem = Activator.CreateInstance(type);
                    for (int j = 1; j <= worksheet.Dimension.End.Column; j++)
                    {
                        string fieldName = worksheet.Cells[FIELD_ROW, j].Value.ToString();

                        FieldInfo property = type.GetField(fieldName);

                        if (property != null)
                        {
                            if (worksheet.Cells[i, j].Value == null || string.IsNullOrEmpty(worksheet.Cells[i, j].Value.ToString()))
                                continue;
                            string cellValue = worksheet.Cells[i, j].Value.ToString();
                            if (property.FieldType == typeof(int))
                            {
                                property.SetValue(dataItem, int.Parse(cellValue));
                            }
                            else if (property.FieldType == typeof(float))
                            {
                                property.SetValue(dataItem, float.Parse(cellValue));
                            }
                            else if (property.FieldType == typeof(string))
                            {
                                property.SetValue(dataItem, cellValue);
                            }
                            else if (property.FieldType == typeof(bool))
                            {
                                property.SetValue(dataItem, bool.Parse(cellValue));
                            }
                            else if (property.FieldType == typeof(double))
                            {
                                property.SetValue(dataItem, double.Parse(cellValue));
                            }
                            else if (property.FieldType == typeof(DateTime))
                            {
                                property.SetValue(dataItem, DateTime.Parse(cellValue));
                            }
                            else if (property.FieldType.IsEnum)
                            {
                                property.SetValue(dataItem, Enum.Parse(property.FieldType, cellValue));
                            }
                            else if (property.FieldType == typeof(long))
                            {
                                property.SetValue(dataItem, long.Parse(cellValue));
                            }
                        }
                    }
                    // 填充到so 的list
                    if (list != null) list.GetType().GetMethod("Add").Invoke(list, new[] { dataItem });
                }
                // AssetDatabase.CreateAsset(excelData, soPath);
                OnFinish?.Invoke(excelData);
                // 标记为脏，确保Unity保存更改
                UnityEditor.EditorUtility.SetDirty(excelData);
            }
            AssetDatabase.SaveAssets();
        }
    }
}
}

#endif